/**
 * @FileName: BlankcardExportService.java
 * @Author
 * @Description:
 * @Date 2016年5月30日 下午3:33:14
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.core.JFinal;
import com.jfinal.kit.StrKit;

import cn.hpclub.server.bean.SystemConfig;
import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.ExcelLargeDataUtils;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.SystemConfigUtil;

public class BlankcardExportService{
    private static final String   STATUS                 = "status";
    private static final String   MESSAGE                = "message";
    private static final String   URL                    = "url";
    private static final String   SQL_ORDER_BY           = "ORDER BY org.id ASC";
    private static final String   SQL_SELECT_4_EXPORT    = "select org.id as orgId,(select gan.DisplayName from organization as gan where gan.id=org.parentid) as parentname,(select class_name from organization_class where id=org.WebsiteId) as class_name,NumCode, DisplayName, SIMCardCount,IFNULL(sp.used,0) as used, (SIMCardCount-IFNULL(used,0)) as freeCount ";
    private static final String   SQL_LEFT_JOIN_4_EXPORT = "LEFT JOIN (select COUNT(org_id) as used, org_id  from sales_promotion where moduleId=3 and return_code = '0' GROUP BY org_id) as sp on org.id = sp.org_id ";
    private static final String[] EXPORT_HEADER          = { "区县分公司", "平台名称", "网点名称", "渠道编码", "网点类型", "白卡买断量", "写卡量",
            "白卡剩余量" };
    private static final String[] EXPORT_COLUMN_NAME     = { "parentname", "parentname", "DisplayName", "NumCode",
            "class_name", "SIMCardCount", "used", "freeCount" };

    private static Logger         log                    = LoggerFactory.getLogger(BlankcardExportService.class);

    /**
     * @Title: exportData
     * @Description:导出白卡数据（查询方式与列表查询不一样，采用的是流数据接收方式，每次只从服务器接收部份数据， 直到所有数据处理完毕
     *                                                             ，不会发生JVM OOM）
     * @param keyword:白卡数据查询关键字
     * @return JSONObject:导出结果JSON对象
     */
    public static JSONObject exportData(String keyword){
        String keywordFilter = getKeywordFilter(keyword);
        String sqlOrgFilter = InterfaceUtil.getChlidOrgByLevelSql(InterfaceUtil.getOrgId(), Constant.MAX_ORG_LEVEL);
        String sql = String.format("%s FROM organization AS org  %s WHERE %s AND org.id IN %s GROUP BY org.id %s ",
                                   SQL_SELECT_4_EXPORT, SQL_LEFT_JOIN_4_EXPORT, keywordFilter, sqlOrgFilter,
                                   SQL_ORDER_BY);
        log.debug("sql=" + sql);
        String fileName = generateFileName();
        String filePath = getFilePath(fileName);
        JSONObject jo = null;
        try{
            String exportFileUrl = ExcelLargeDataUtils.exportData(EXPORT_HEADER, EXPORT_COLUMN_NAME, sql, fileName,
                                                                  filePath);
            jo = createDownloadSuccessJSONObject(exportFileUrl);
        }
        catch(IOException e){
            e.printStackTrace();
            jo = createDownloadErrorJSONObject(e);
        }
        return jo;
    }

    private static JSONObject createDownloadErrorJSONObject(IOException e){
        JSONObject jo = new JSONObject();
        jo.put(STATUS, 0);
        jo.put(MESSAGE, "导出失败：" + e.getMessage());
        return jo;
    }

    private static JSONObject createDownloadSuccessJSONObject(String exportFileUrl){
        JSONObject jo = new JSONObject();
        jo.put(STATUS, 1);
        jo.put(MESSAGE, "导出成功，点击下载");
        jo.put(URL, exportFileUrl);
        return jo;
    }

    private static String getKeywordFilter(String keyword){
        String keywordFilter = "";
        if(!StrKit.isBlank(keyword)){
            keywordFilter = String.format(" org.DisplayName like '%s%s%s'", "%", keyword, "%");
        } else{
            keywordFilter = " 1 ";
        }
        return keywordFilter;
    }

    private static String getFilePath(String fileName){
        String filePath = JFinal.me().getServletContext().getRealPath(SystemConfig.UPLOAD_FILE_DIR + fileName);
        String fileDir = SystemConfigUtil.getSystemConfig().getUploadDir();
        log.info("fileDir = " + fileDir);

        if(!StringUtils.isEmpty(fileDir)){
            filePath = String.format("%s/file/%s", fileDir, fileName);
        }
        return filePath;
    }

    private static String generateFileName(){
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HHmmssSSS");
        String curTime = sdf.format(date);
        String fileName = String.format("白卡报表%s.xlsx", curTime);
        return fileName;
    }
}
